{Srv} = require "../Srv"
mysql = require "mysql"
fs = require "fs"
zlib = require "zlib"
path = require "path"
Hzip = require "hzip"
ejsExcel = require "ejsExcel"
fsAsync = require "fsAsync"

SupperClzz = Srv
exports.SysSrv = new Class
  Extends: SupperClzz
  options:
    #表
    tab: undefined
    #页面名称
    page: undefined
    pgType: "add"
    #搜索条件
    seaArr: []
    #排序
    sortArr: []
    #h:include标签引入的页面的Clz
    hIncClzArr: []
    #不受登录超时影响的方法
    ssnArr: [[undefined,"_sessionId"],["sys.LoginSrv","login"]]
    #需要服务器端验证的字段,保存和修改时验证
    $vdts: []
    #是否是树形结构的页面
    is_tree: false
    #默认排序字段
    sort_fld: "id asc"
    #是否启用字段
    enable_fld: undefined
  onDraw: ->
    t = this
    o = t.options
    yield Srv.prototype.onDraw.apply t,arguments
    return
  "@runAllVdts":{comment:"执行$vdts里面的所有验证"}
  runAllVdts: (reqOpt,eny,keyArr)->
    t = this
    o = t.options
    keyArr = o.$vdts if !keyArr
    for key in keyArr
      continue if !t[key+"Vdt"] or !eny.hasOwnProperty key
      val = eny[key]
      isPass = yield t[key+"Vdt"] reqOpt,key,val,eny.id
      throw isPass.err if isPass isnt true and isPass.err
    true
  "@oid2uid":{comment:"大对象oid转换为提供文件下载的uid"}
  oid2uid: (reqOpt,oid,tab)->
    t = this
    o = t.options
    return if !oid
    tab = tab or o.tab
    tabEd = t.escapeId tab
    name = "lo_export"+String.uniqueID()
    tmpFile = _PROJECT_PATH+"tmp/"+name
    sql = """
    select lo_export($1,'#{tmpFile}') from #{tabEd}
    """
    yield t.callSql reqOpt,sql,[oid]
    uid = yield t.buf2uid reqOpt,tmpFile,name,(path2)-> yield fsAsync.unlinkAsync path2
    uid
  "@sortAdd":{comment:"增加排序,自动覆盖同名的排序"}
  sortAdd: (reqOpt,srtObj)->
    t = this
    o = t.options
    throw new Error srtObj if !srtObj.sort_fld
    srtObj.dirt = srtObj.dirt or "asc"
    throw new Error srtObj if srtObj.dirt isnt "asc" and srtObj.dirt isnt "desc"
    index = undefined
    for srtTmp,i in o.sortArr
      if srtObj.sort_fld is srtTmp.sort_fld
        o.sortArr[i] = srtObj
        index = i
        break
    if index is undefined
      index = o.sortArr.push(srtObj)-1
    index
  "@sortDel":{comment:"删除排序"}
  sortDel: (reqOpt,sort_fld)->
    t = this
    o = t.options
    indexArr = []
    for srtObj,i in o.sortArr
      if srtObj.sort_fld is sort_fld
        indexArr.push i
    for index in indexArr
      o.sortArr.splice index,1
    return
  "@srchAdd":{comment:"增加搜索条件,返回条件所在位置"}
  srchAdd: (reqOpt,seaObj)->
    t = this
    o = t.options
    throw new Error seaObj if seaObj.andOr isnt "and" and seaObj.andOr isnt "or"
    throw new Error seaObj if ["=",">=",">","<","<=","begin","end","like","!="].indexOf(seaObj.opt) is -1
    seaArr = o.seaArr
    for seaTmp,i in seaArr
      if seaTmp is undefined or seaTmp is null
        seaArr[i] = seaObj
        return i
    idx = seaArr.push(seaObj)-1
    idx
  "@srchDel":{comment:"删除第几个搜索条件"}
  srchDel: (reqOpt,idx)->
    t = this
    o = t.options
    seaArr = o.seaArr
    seaArr[idx] = undefined
    return
  "@srch2Whr":{comment:"""
    将搜索条件转换为where字符串
      argArr 之前的参数列表
      seaArr 搜索条件
      isWhr 是否加上 where 字样
  """}
  srch2Whr: (reqOpt,argArr,seaArr,isWhr)->
    t = this
    o = t.options
    whr = " "
    seaArr = seaArr or o.seaArr
    num = 0
    for seaObj in seaArr
      continue if seaObj is undefined or seaObj is null
      continue if seaObj.andOr isnt "and" and seaObj.andOr isnt "or"
      continue if ["=",">=",">","<","<=","begin","end","like","!="].indexOf(seaObj.opt) is -1
      value = seaObj.value
      opt = seaObj.opt
      if opt is "begin"
        opt = "like"
        value = value+"%"
      else if opt is "end"
        opt = "like"
        value = "%"+value
      else if opt is "like"
        value = "%"+value+"%"
      index = argArr.push value
      if isWhr
        if num is 0
          whr += "where "
        else
          whr += " "+seaObj.andOr+" "
      else
        whr += " "+seaObj.andOr+" "
      nameArr = seaObj.name.split "\."
      if nameArr[0]
        whr += t.escapeId nameArr[0]
      if nameArr[1]
        whr += "."
        whr += t.escapeId nameArr[1]
      whr += " "+opt+" $"+index
      num++
    whr
  "@sort2Ody":{comment:"拼装排序的字符串"}
  sort2Ody: (reqOpt,sortArr,isOrderBy)->
    t = this
    o = t.options
    str = ""
    for srtObj,i in o.sortArr
      if i is 0
        str += " order by " if isOrderBy is true
      sort_fldArr = srtObj.sort_fld.split "\."
      if sort_fldArr[0]
        str += t.escapeId sort_fldArr[0]
      if sort_fldArr[1]
        str += "."
        str += t.escapeId sort_fldArr[1]
      str += " "+srtObj.dirt+","
    str
  dataGrid: (reqOpt,pgOffset,pgNum,tab)->
    t = this
    o = t.options
    pgOffset = Number pgOffset
    pgOffset = 0 if isNaN pgOffset
    pgNum = Number pgNum
    pgNum = 0 if isNaN pgNum
    return [] if pgNum is 0
    tab = tab or o.tab
    tabEd = t.escapeId tab
    argArr = [pgNum,pgOffset]
    sql = "select * from #{tabEd} t"
    sql += t.srch2Whr reqOpt,argArr,o.seaArr,true
    sql += " order by "
    sql += t.sort2Ody reqOpt,o.sortArr
    sql += "t.id asc"
    sql += " limit $1 offset $2"
    rltSet = yield t.callSql reqOpt,sql,argArr
    rltSet = rltSet.rows
    rltSet
  dataCount: (reqOpt,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    argArr = []
    sql = "select count(t.id) as count from #{tabEd} t"
    sql += t.srch2Whr reqOpt,argArr,o.seaArr,true
    rltSet = yield t.callSql reqOpt,sql,argArr
    rltSet = rltSet.rows
    rltSet[0].count
  findById: (reqOpt,id,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    rltSet = yield t.callSql reqOpt,"select t.* from "+tabEd+" t where t.id=$1",[id]
    rltSet = rltSet.rows
    entry = rltSet[0]
    entry
  #通过id删除一条记录
  delById: (reqOpt,id,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    rltSet = yield t.callSql reqOpt,"delete from "+tabEd+" where id=$1",[id]
    rltSet
  "@treAllPrnId":{comment:"获得当前eny的所有祖宗id号的数组,从根节点往下排序"}
  treAllPrnId: (reqOpt,id,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    tab_tree_pathEd = t.escapeId tab+"_tree_path"
    rltSet = yield t.callSql reqOpt,"""
    select m.id
    from #{tabEd} m 
    join #{tab_tree_pathEd} mp 
      on mp.ancestor=m.id 
    where 
      mp.descendant=$1
    order by
      mp.path_length desc
    """,[id]
    rltSet = rltSet.rows
    rltSet
  "@treInsert": {isTran:true,comment:"""
    树节点增加
  """}
  treInsert: (reqOpt,entry,keyArr,returning,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    tab_tree_pathEd = t.escapeId tab+"_tree_path"
    returning = returning or []
    returning.push "id"
    rltSet = yield t.saveAddClk reqOpt,entry,keyArr,returning,tab
    id = rltSet[0].id
    prn_id = entry.prn_id
    sql = """
    insert into #{tab_tree_pathEd}(ancestor,descendant,path_length)
      select mp.ancestor,#{id},mp.path_length+1
      from #{tab_tree_pathEd} as mp
      where mp.descendant=$2
      union
      select $1,$1,0
    """
    yield t.callSql reqOpt,sql,[id,prn_id]
    rltSet
  "@treUpdate":{isTran:true,comment:"""
    树节点更新,移除自树,然后再将子树插入到新的父亲中去
      若prn_id为0时,代表此节点是根节点,is_root修改为true
      如果没有这个逻辑,那么此节点会被悬空,不是任何人的孩子
  """}
  treUpdate: (reqOpt,entry,keyArr,returning,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    tab_tree_pathEd = t.escapeId tab+"_tree_path"
    id = entry.id
    prn_id = entry.prn_id
    #prn_id 可能为0
    if prn_id isnt undefined and prn_id isnt null
      sql = """
      delete from #{tab_tree_pathEd} tp2 where tp2.ancestor in (
        select tp1.ancestor from #{tab_tree_pathEd} tp1 where tp1.descendant=$1 and tp1.path_length>0
      ) and tp2.descendant in (
        select tp3.descendant from #{tab_tree_pathEd} tp3 where tp3.ancestor=$1
      )
      """
      yield t.callSql reqOpt,sql,[id]
      sql = """
      insert into #{tab_tree_pathEd}(ancestor,descendant,path_length)
        select mp.ancestor,tp1.descendant,mp.path_length+tp1.path_length+1
        from #{tab_tree_pathEd} as mp
        join #{tab_tree_pathEd} tp1
            on tp1.ancestor=$1
        where mp.descendant=$2
      """
      yield t.callSql reqOpt,sql,[id,prn_id]
    entry.is_root = true if Number(prn_id) is 0
    rltSet = yield t.update reqOpt,entry,keyArr,returning,tab
    rltSet
  "@treDelNdById":{isTran:true,comment:"通过id号删除树结构的一个节点,后面的节点往上提,此类所有tre开头的方法都是跟操作树相关的"}
  treDelNdById: (reqOpt,id,tab)->
    t = this
    o = t.options
    yield t.treNdUp reqOpt,id,tab
    rltSet = yield t.delById reqOpt,id,tab
    rltSet
  "@treNdUp":{isTran:true,comment:"""
    树节点往上提一层,id节点的所有子孙path_legnth-1
    首先获得此节点的父亲,然后获得此节点的所有的子孙,然后此父亲下的所有子孙path_legnth-1
  """}
  treNdUp: (reqOpt,id,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    tab_tree_pathEd = t.escapeId tab+"_tree_path"
    yield t.callSql reqOpt,"""
    update #{tab_tree_pathEd}
    set path_length=path_length-1
    where id in (
      select tp3.id
      from #{tab_tree_pathEd} tp3
      join #{tab_tree_pathEd} tp1
        on tp1.descendant=$1 and tp1.path_length=1
      join #{tab_tree_pathEd} tp2
        on tp2.ancestor=$1
      where
        tp3.ancestor=tp1.ancestor
        and tp3.descendant=tp2.descendant
    )
    """,[id]
    yield t.callSql reqOpt,"delete from "+tab_tree_pathEd+" where ancestor=$1 and descendant=$1",[id]
    return
  update: (reqOpt,entry,keyArr,returning,tab)->
    t = this
    o = t.options
    throw new Error "entry.id can not be empty!" if String.isEmpty entry.id
    yield t.runAllVdts reqOpt,entry
    tab = tab or o.tab
    tabEl = t.escapeId tab
    sql = "update "+tabEl+" set "
    argArr = []
    for key,i in keyArr
      val = entry[key]
      continue if val is undefined
      argArr.push val
      sql += t.escapeId(key)+"=$"+argArr.length+","
    return true if argArr.length is 0
    sql = sql.substring 0,sql.length-1 if sql.charAt(sql.length-1) is ","
    argArr.push entry.id
    sql += " where id=$"+argArr.length
    rltSet = yield t.callSql reqOpt,sql,argArr
    rltSet
  "@sqlValStrFn":{comment:"给saveAddClk调用",_private:true}
  sqlValStrFn: (reqOpt,key,val,argArr)->
    sqlValStr = ""
    if val is undefined
      sqlValStr += "default"
    else
      argArr.push val
      sqlValStr += "$"+argArr.length
    sqlValStr += ","
    sqlValStr
  "@saveAddClk":{isTran:true,comment:"""
    保存一条记录
      @entry 需要保存的实体类,例如:{name:"ab",username:"un"}
      @keyArr 需要保存的key,例如:["name","useranme"]
      @return 返回returning数组指定的列,特殊情况,如果returning是*则返回所有列,例如:returning为["id"]时,返回{id:2}
  """}
  saveAddClk: (reqOpt,entry,keyArr,returning,tab)->
    t = this
    o = t.options
    delete entry.id
    yield t.runAllVdts reqOpt,entry
    tab = tab or o.tab
    tab = t.escapeId tab
    sql = "insert into "+tab+" ("
    sqlValStr = ""
    argArr = []
    for key in keyArr
      val = entry[key]
      sql += t.escapeId key
      sql += ","
      sqlValStr += t.sqlValStrFn reqOpt,key,val,argArr
    if keyArr.length isnt 0
      sql = sql.substring 0,sql.length-1
      sqlValStr = sqlValStr.substring 0,sqlValStr.length-1
    sql += ") values ("+sqlValStr+")"
    if returning is "*"
      sql += " returning *"
    else if returning
      typeTmp = typeOf returning
      returning = [returning] if typeTmp isnt "array"
      sql += " returning "
      for str,i in returning
        sql += t.escapeId str
        sql += "," if returning.length-1 isnt i
    rltSet = yield t.callSql reqOpt,sql,argArr
    rltSet = rltSet.rows
    rltSet
  "@confirmButClk":{isTran:true,comment:"点击确定按钮"}
  confirmButClk: (reqOpt,entry,keyArr,returning,tab)->
    t = this
    o = t.options
    rltSet = undefined
    if o.is_tree
      rltSet = yield t.treInsert reqOpt,entry,keyArr,returning,tab
    else
      rltSet = yield t.saveAddClk reqOpt,entry,keyArr,returning,tab
    rltSet
  #获得树的根节点,可能有很多个
  treeRoot: (reqOpt,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    sql = """
    select t.*
    from #{tabEd} t 
    where 
      t.is_root=true
      and t.is_leaf=false
    """
    if o.enable_fld
      sql += " and t."+o.enable_fld+"=true"
    if o.sort_fld
      sql += " order by t."+o.sort_fld
    rltSet = yield t.callSql reqOpt,sql
    rltSet = rltSet.rows
    rltSet
  "@treeCld":{comment:"获得树形结构的孩子们"}
  treeCld: (reqOpt,id,tab)->
    t = this
    o = t.options
    tab = tab or o.tab
    tabEd = t.escapeId tab
    tree_pathEd = t.escapeId tab+"_tree_path"
    sql = """
    select t.*
    from #{tabEd} t 
    join #{tree_pathEd} mp 
      on t.id=mp.descendant
    where 
      mp.ancestor=$1
      and mp.path_length=1
      and t.is_leaf=false
    """
    if o.enable_fld
      sql += " and t."+o.enable_fld+"=true"
    if o.sort_fld
      sql += " order by t."+o.sort_fld
    rltSet = yield t.callSql reqOpt,sql,[id]
    rltSet = rltSet.rows
    rltSet
  xlxImportEn: (reqOpt,file)->
    t = this
    o = t.options
    reqOpt.res.setHeader "Content-Type", "text/plain;charset=utf-8"
    if file.name.trim() is ""
      yield fsAsync.unlinkAsync file.path
      throw "Sorry,You must select a file first!"
    extname = path.extname file.name
    if extname isnt ".xlsx"
      yield fsAsync.unlinkAsync file.path
      throw new Error file.name+" must ends with .xlsx"
    buffer = yield readFileAsync file.path
    #删除临时文件
    yield fsAsync.unlinkAsync file.path
    
    sharedStr = null
    sheets = []
    hzip = new Hzip buffer
    entries = hzip.entries
    for entry in entries
      fileName = entry.fileName
      if fileName is "xl/sharedStrings.xml" or fileName is "xl/worksheets/sheet1.xml"
        buf = yield inflateRawAsync entry.cfile
        if fileName is "xl/worksheets/sheet1.xml" then sheets.push buf
        else sharedStr = buf
    sheetsObj = ejsExcel.getExcelEns sharedStr,sheets
    sheetsEns = sheetsObj.sheetsEns
    sheetsEns
  hInclude: (reqOpt,hIncClzArr)->
    t = this
    o = t.options
    o.hIncClzArr = hIncClzArr
    return
  _applyMd: (reqOpt,mdStr,pms)->
    t = this
    o = t.options
    srvPath = reqOpt.srvPath
    pass = true
    if !t.session.emp
      pass = false
      for item in o.ssnArr
        if (!item[0] or item[0] is srvPath) and (!item[1] or item[1] is mdStr)
          pass = true
          break
    if !pass
      reqOpt.action = ["sttConfirm"]
      return
    rv = yield SupperClzz.prototype._applyMd.apply t,arguments
    rv
